# 访问sqlite数据库
import sqlite3
from typing import List, Optional
from config import app_config 


class CharacterCardRepository:
    """
    角色卡表(character_cards)的数据访问层
    表结构:
    - id: integer primary key autoincrement
    - name: varchar(255) not null (角色名称)
    - avatar_url: varchar(255) (头像URL)
    - faction: varchar(255) (阵营)
    - title: varchar(255) (称号)
    - personality: text (性格)
    - speech_style: text (说话风格)
    - background: text (背景故事)
    - catchphrases: text (口头禅)
    - behavior_patterns: text (行为模式)
    - prohibited: text (禁止内容)
    - dialogue_examples: text (对话示例)
    - interaction_rules: text (互动规则)
    - temperature: float default 0.7 (温度参数)
    - top_p: float default 0.9 (Top-P参数)
    - system_prompt: text (系统提示词)
    - enabled: boolean default 1 (是否启用)
    - created_at: datetime default (datetime('now', 'localtime')) (创建时间)
    - updated_at: datetime default (datetime('now', 'localtime')) (更新时间)
    - deleted: boolean default 0 (软删除标志)
    """

    def __init__(self):
        """初始化数据库连接"""
        self.db_path = app_config.sqlite_db_path

    def _get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # 使结果可以通过列名访问
        return conn

    def create_character_card(self, character_data: dict) -> int:
        """
        创建新的角色卡记录
        
        Args:
            character_data: 角色卡数据字典，包含以下字段：
                - name: 角色名称
                - avatar_url: 头像URL (可选)
                - faction: 阵营 (可选)
                - title: 称号 (可选)
                - personality: 性格 (可选)
                - speech_style: 说话风格 (可选)
                - background: 背景故事 (可选)
                - catchphrases: 口头禅 (可选)
                - behavior_patterns: 行为模式 (可选)
                - prohibited: 禁止内容 (可选)
                - dialogue_examples: 对话示例 (可选)
                - interaction_rules: 互动规则 (可选)
                - temperature: 温度参数，默认0.7
                - top_p: Top-P参数，默认0.9
                - system_prompt: 系统提示词 (可选)
                - enabled: 是否启用，默认True (1)
            
        Returns:
            int: 新创建记录的ID
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            
            # 从字典中提取字段值，设置默认值
            name = character_data.get('name')
            avatar_url = character_data.get('avatar_url')
            faction = character_data.get('faction')
            title = character_data.get('title')
            personality = character_data.get('personality')
            speech_style = character_data.get('speech_style')
            background = character_data.get('background')
            catchphrases = character_data.get('catchphrases')
            behavior_patterns = character_data.get('behavior_patterns')
            prohibited = character_data.get('prohibited')
            dialogue_examples = character_data.get('dialogue_examples')
            interaction_rules = character_data.get('interaction_rules')
            temperature = character_data.get('temperature', 0.7)
            top_p = character_data.get('top_p', 0.9)
            system_prompt = character_data.get('system_prompt')
            
            cursor.execute("""
                INSERT INTO character_cards
                (name, avatar_url, faction, title, personality, speech_style, background, catchphrases,
                behavior_patterns, prohibited, dialogue_examples, interaction_rules, temperature, top_p,
                system_prompt, enabled, deleted)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (name, avatar_url, faction, title, personality, speech_style, background, catchphrases,
                  behavior_patterns, prohibited, dialogue_examples, interaction_rules, temperature, top_p,
                  system_prompt, 1, 0))  # 将False改为0，表示未删除
            conn.commit()
            result = cursor.lastrowid or 0
        finally:
            conn.close()
        return result

    def get_character_card_by_id(self, character_id: int) -> Optional[dict]:
        """
        根据ID获取角色卡记录
        
        Args:
            character_id: 角色ID
            
        Returns:
            dict: 角色卡记录或None（如果未找到）
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, name, avatar_url, faction, title, personality, speech_style, background,
                       catchphrases, behavior_patterns, prohibited, dialogue_examples, interaction_rules,
                       temperature, top_p, system_prompt, enabled, created_at, updated_at
                FROM character_cards
                WHERE id = ?
            """, (character_id,))
            row = cursor.fetchone()
            result = dict(row) if row else None
        finally:
            conn.close()
        return result

    def get_character_card_by_name(self, name: str) -> List[dict]:
        """
        根据名称获取角色卡记录
        
        Args:
            name: 角色名称
            
        Returns:
            dict: 角色卡记录或None（如果未找到）
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, name, avatar_url, faction, title, personality, speech_style, background,
                       catchphrases, behavior_patterns, prohibited, dialogue_examples, interaction_rules,
                       temperature, top_p, system_prompt, enabled, created_at, updated_at
                FROM character_cards
                WHERE name like ? AND deleted = 0
            """, (f"%{name}%",))
            rows = cursor.fetchall()
            result = [dict(row) for row in rows]
        finally:
            conn.close()
        return result

    def get_all_character_cards(self) -> List[dict]:
        """
        获取所有角色卡记录
        
        Returns:
            List[dict]: 所有角色卡记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, name, avatar_url, faction, title, personality, speech_style, background,
                       catchphrases, behavior_patterns, prohibited, dialogue_examples, interaction_rules,
                       temperature, top_p, system_prompt, enabled, created_at, updated_at
                FROM character_cards
                WHERE deleted = 0
                ORDER BY created_at ASC
            """)
            rows = cursor.fetchall()
            result = [dict(row) for row in rows]
        finally:
            conn.close()
        return result

    def get_enabled_character_cards(self) -> List[dict]:
        """
        获取所有启用的角色卡记录
        
        Returns:
            List[dict]: 启用的角色卡记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, name, avatar_url, faction, title, personality, speech_style, background,
                       catchphrases, behavior_patterns, prohibited, dialogue_examples, interaction_rules,
                       temperature, top_p, system_prompt, enabled, created_at, updated_at
                FROM character_cards
                WHERE enabled = 1 AND deleted = 0
                ORDER BY created_at ASC
            """)
            rows = cursor.fetchall()
            result = [dict(row) for row in rows]
        finally:
            conn.close()
        return result

    def update_character_card(self, character_id: int, **kwargs) -> bool:
        """
        更新角色卡记录
        
        Args:
            character_id: 角色ID
            **kwargs: 要更新的字段和值
            
        Returns:
            bool: 更新是否成功
        """
        if not kwargs:
            return False

        # 构建动态更新语句
        fields = []
        values = []
        for key, value in kwargs.items():
            # 确保字段名是表中的有效列
            valid_fields = {
                'name', 'avatar_url', 'faction', 'title', 'personality', 'speech_style', 'background',
                'catchphrases', 'behavior_patterns', 'prohibited', 'dialogue_examples', 'interaction_rules',
                'temperature', 'top_p', 'system_prompt', 'enabled'
            }
            if key in valid_fields:
                fields.append(f"{key} = ?")
                values.append(value)

        # 如果没有有效的字段要更新，则返回False
        if not fields:
            return False

        # 添加更新时间和角色ID到值列表
        values.append(character_id)

        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(f"""
                UPDATE character_cards
                SET {', '.join(fields)}, updated_at = datetime('now', 'localtime')
                WHERE id = ? 
            """, values)
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result

    def delete_character_card(self, character_id: int) -> bool:
        """
        软删除角色卡记录
        
        Args:
            character_id: 角色ID
            
        Returns:
            bool: 删除是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE character_cards
                SET deleted = 1, updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (character_id,))
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result

    def restore_character_card(self, character_id: int) -> bool:
        """
        恢复软删除的角色卡记录
        
        Args:
            character_id: 角色ID
            
        Returns:
            bool: 恢复是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE character_cards
                SET deleted = 0, updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (character_id,))
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result

    def hard_delete_character_card(self, character_id: int) -> bool:
        """
        硬删除角色卡记录（永久删除）
        
        Args:
            character_id: 角色ID
            
        Returns:
            bool: 删除是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM character_cards WHERE id = ?", (character_id,))
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result
    
    def band_character_card(self, character_id: int) -> bool:
        """
        禁用角色卡
        
        Args:
            character_id: 角色ID
            
        Returns:
            bool: 禁用是否成功
        """
        return self.update_character_card(character_id, enabled=0)
    
    def start_character_card(self, character_id: int) -> bool:
        """
        启用角色卡
        
        Args:
            character_id: 角色ID
            
        Returns:
            bool: 启用是否成功
        """
        return self.update_character_card(character_id, enabled=1)

    def build_system_prompt(self, character_id: int) -> str:
        """
        根据角色卡构建系统提示词
        
        Args:
            character_id: 角色ID
            
        Returns:
            str: 构建的系统提示词
        """
        character = self.get_character_card_by_id(character_id)
        if not character:
            raise ValueError(f"Character card with ID {character_id} not found.")

        prompt_parts = [
            f"You are {character['name']}.",
            f"Title: {character.get('title', 'N/A')}.",
            f"Faction: {character.get('faction', 'N/A')}.",
            f"Personality: {character.get('personality', 'N/A')}.",
            f"Speech Style: {character.get('speech_style', 'N/A')}.",
            f"Background: {character.get('background', 'N/A')}.",
            f"Catchphrases: {character.get('catchphrases', 'N/A')}.",
            f"Behavior Patterns: {character.get('behavior_patterns', 'N/A')}.",
            f"Prohibited Content: {character.get('prohibited', 'N/A')}.",
            f"Dialogue Examples: {character.get('dialogue_examples', 'N/A')}.",
            f"Interaction Rules: {character.get('interaction_rules', 'N/A')}."
        ]

        # 如果角色卡中有自定义系统提示词，则添加
        if character.get('system_prompt'):
            prompt_parts.append(f"Additional Instructions: {character['system_prompt']}")

        return "\n".join(prompt_parts)

    def get_model_params(self, character_id: int) -> dict:
        """
        获取角色卡的模型参数（temperature和top_p）
        
        Args:
            character_id: 角色ID
            
        Returns:
            dict: 包含temperature和top_p的字典
        """
        character = self.get_character_card_by_id(character_id)
        if not character:
            raise ValueError(f"Character card with ID {character_id} not found.")
        
        return {
            "temperature": character.get("temperature"),
            "top_p": character.get("top_p")
        }

# 创建全局实例
character_card_repo = CharacterCardRepository()